package com.raychi.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.raychi.demo.entity.Student;
import com.raychi.demo.util.StudentUtil;

public class StudentDao extends SqlMapClientDaoSupport{
	
	public Student getStudentById(int id) throws ClassNotFoundException, SQLException{
		return (Student) super.getSqlMapClientTemplate().queryForObject("student.getStudentById", id);
	}
	/**
	 * 将指定id的学生信息删除
	 * @param student 用页面获取的一个指定的id
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void delete(int id) throws Exception{
		super.getSqlMapClientTemplate().delete("student.delete", id);
	}
	/**
	 * 将注册成功的用户存入数据库
	 * @param student 从页面获取的用户注册时填写的信息
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void save(Student student, Connection conn) throws Exception{
		String sql = "insert into Student (name, age, username, password) values (?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, student.getName());
		ps.setInt(2, student.getAge());
		ps.setString(3, student.getUsername());
		ps.setString(4, student.getPassword());
		ps.execute();
	}
	/**
	 * 将更改好的学生信息存入数据库
	 * @param student 从页面输入的更改信息
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void update(Student student) throws Exception{
		super.getSqlMapClientTemplate().update("student.update", student);
	}
	/**
	 * 通过用户名和密码来查询对应的Student的信息
	 * @param conn 连接数据库
	 * @param username 登陆时的用户名
	 * @param password 登陆时的密码
	 * @return 对应学生的信息
	 * @throws SQLException
	 */
	public Student getStudentByUsernameAndPassword(Connection conn, String username, String password) throws SQLException {
		String sql = "select * from Student where username=? and password=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();
		Student student = null;
		while (rs.next()) {
			student = StudentUtil.createStudent(rs);
		}
		return student;
	}
	/**
	 * 通过username来查询和统计数据库中相同username的数量
	 * @param conn 连接数据库
	 * @param username 注册时填写的用户名
	 * @return 相同用户名的数量
	 * @throws SQLException
	 */
	public int getCountStudentByUsername(Connection conn, String username) throws SQLException {
		String sql = "select count(*) from Student where username='" + username + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		int rsl = 0;
		while (rs.next()) {
			rsl = rs.getInt(1);
		}
		return rsl;
	}
	/**
	 * 从数据库获取Student的信息 
	 * @param conn 调用数据库
	 * @return	所有学生的信息
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public List<Student> listStudent(Connection conn) throws ClassNotFoundException, SQLException{
		List<Student> students = new ArrayList<Student>();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("select * from student");
		Student student = null;
		while(rs.next()){
			student = StudentUtil.createStudent(rs);
			students.add(student);
		}
		return students;
	}
	
	
}
